import pandas as pd
from sqlalchemy import create_engine, and_
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, ForeignKey, String, Text, JSON, Float
from sqlalchemy.orm import relationship

# 数据库配置
# DATABASE_CONFIG = {
#     'host': '172.29.228.247',
#     'port': 3307,
#     'user': 'iflytek',
#     'password': '74061c0a071a',
#     'database': 'estimate_dev_new',
#     'ssl_disabled': True,
#     'connect_timeout': 1200,
#     'timeout': 1200
# }

DATABASE_CONFIG = {
    'host': '172.29.247.245',
    'port': 3306,
    'user': 'root',
    'password': '123456',
    'database': 'estimate_prod',
    'ssl_disabled': True,
    'connect_timeout': 1200,
    'timeout': 1200
}

# 创建数据库连接 URL
DATABASE_URL = f"mysql+pymysql://{DATABASE_CONFIG['user']}:{DATABASE_CONFIG['password']}@{DATABASE_CONFIG['host']}:{DATABASE_CONFIG['port']}/{DATABASE_CONFIG['database']}?charset=utf8mb4"

# 创建 SQLAlchemy 引擎
engine = create_engine(DATABASE_URL, echo=False)

# 创建 Session
Session = sessionmaker(bind=engine)
db_session = Session()

# 定义 Base
Base = declarative_base()


# 定义 EvaluateDetail 类
class EvaluateDetail(Base):
    __tablename__ = 'evaluate_result_detail'

    _id = Column('id', Integer, primary_key=True, autoincrement=True, doc='主键ID')
    _taskId = Column('taskId', Integer, ForeignKey('task.id'), nullable=False, doc='关联任务表ID')
    _questionId = Column('questionId', Integer, nullable=False, doc='问题编号')
    _modelId = Column('modelId', Integer, ForeignKey('model.id'), nullable=False, doc='关联模型表ID')
    _dataset = Column('dataset', String(50), nullable=False, doc='数据集版本')
    _scene = Column('scene', String(50), nullable=False, doc='场景')
    _sceneCode = Column('sceneCode', String(50), nullable=False, doc='场景编码')
    _language = Column('language', String(50), nullable=False, doc='语言')
    _normContentVal = Column('normContentVal', JSON, nullable=False, doc='关联指标内容')
    _caseContent = Column('caseContent', Text, nullable=False, doc='用例内容')
    _keyWords = Column('keyWords', String(255), nullable=False, doc='关键字')
    _normScore = Column('normScore', Float, nullable=False, doc='指标得分')
    _modelRet = Column('modelRet', Text, nullable=True, doc='模型结果')
    _standardRet = Column('standardRet', Text, nullable=True, doc='参考答案')
    _createTime = Column('createTime', String(50), nullable=False, doc='创建时间')
    _updateTime = Column('updateTime', String(50), nullable=False, doc='修改时间')
    _deleted = Column('deleted', Integer, nullable=False, default=0, doc='删除标记')

    # 关联到Model
    model = relationship("Model", back_populates="evaluate_result_details")

    @property
    def questionId(self):
        return self._questionId

    @property
    def taskId(self):
        return self._taskId

    @property
    def modelId(self):
        return self._modelId

    @property
    def dataset(self):
        return self._dataset

    @property
    def scene(self):
        return self._scene

    @property
    def language(self):
        return self._language

    @property
    def normScore(self):
        return self._normScore

    @property
    def normContentVal(self):
        return self._normContentVal

    @property
    def caseContent(self):
        return self._caseContent

    @property
    def keyWords(self):
        return self._keyWords

    @property
    def modelRet(self):
        return self._modelRet

    @property
    def standardRet(self):
        return self._standardRet

    @property
    def createTime(self):
        return self._createTime

    @property
    def updateTime(self):
        return self._updateTime

    @property
    def deleted(self):
        return self._deleted


# 定义 Model 类（简化的关联类）
class Model(Base):
    __tablename__ = 'model'

    _id = Column('id', Integer, primary_key=True, autoincrement=True, doc='主键id')
    _name = Column('name', String(50), nullable=False, doc='模型名称')
    _version = Column('version', String(50), nullable=False, doc='版本号')
    _ways = Column('ways', Integer, nullable=False, doc='模型路数')
    _usedWays = Column('usedWays', Integer, nullable=False, doc='已使用路数')
    _size = Column('size', String(50), nullable=False, doc='尺寸')
    _path = Column('path', String(128), nullable=False, doc='部署位置')
    _modelType = Column('modelType', String(128), nullable=False, doc='模型类型')
    _url = Column('url', String(128), nullable=True, doc='连接地址')
    _description = Column('description', String(50), nullable=True, doc='应用场景')
    _createTime = Column('createTime', String(50), nullable=True, doc='创建时间')
    _updateTime = Column('updateTime', String(50), nullable=True, doc='更新时间')
    _status = Column('status', String(50), nullable=False, doc='状态')

    evaluate_result_details = relationship("EvaluateDetail", back_populates="model")

    @property
    def name(self):
        return self._name

    @name.setter
    def name(self, value):
        if not value:
            raise ValueError("Name cannot be empty")
        self._name = value


# 查询数据并导出到 Excel
def export_to_excel():
    # 查询 EvaluateDetail 表
    results = db_session.query(EvaluateDetail).join(Model).filter(
        and_(
            EvaluateDetail._questionId > 0,
            # 场景： 如代码翻译、代码解释
            EvaluateDetail._scene == '代码补全',
            # 来自飞轮平台的"版本号"
            EvaluateDetail._dataset.in_(['V4.3']),
            # 模型的id，来自于model表
            EvaluateDetail._modelId.in_([29, 30,31])
        )
    ).all()

    # 将结果转换为字典列表
    data = [{
        'id': result.questionId,
        '问题编号': result.questionId,
        '任务编号': result.taskId,
        '模型ID': result.modelId,
        '模型名称': result.model.name,
        '场景': result.scene,
        '语言': result.language,
        '指标总分值': result.normScore,
        '指标内容值': result.normContentVal,
        '关键字': result.keyWords,
        '用例名称': result.caseContent,
        '模型结果': result.modelRet,
        '参考答案': result.standardRet,
        '数据集': result.dataset,
        '创建时间': result.createTime
    } for result in results]

    # 创建 DataFrame 并直接指定列名
    df = pd.DataFrame(data, columns=['id', '问题编号', '任务编号', '模型ID', '模型名称', '场景', '语言', '指标总分值',
                                     '指标内容值',
                                     '关键字', '用例名称', '模型结果', '参考答案', '数据集', '创建时间'])

    # 导出到 Excel
    excel_file_path = r'D:\data1\代码补全_20250016_01.xlsx'
    df.to_excel(excel_file_path, index=False)

    # 关闭会话
    db_session.close()
    print(f"数据已成功写入 {excel_file_path}")


# 执行导出
if __name__ == "__main__":
    export_to_excel()
